﻿using DiaoPaiDaYin;
using DXApplicationTangche.DTO;
using DXApplicationTangche.model;
using DXApplicationTangche.UC.门店下单.DTO;
using DXApplicationTangche.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DXApplicationTangche.service
{
    /// <summary>
    /// 设计点服务
    /// </summary>
    public class ItemService
    {
        /// <summary>
        /// 查询默认项
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static List<设计点图片Dto> DefaultItem(String id)
        {
            String sql = "SELECT\n" +
                "	a.ITEM_CD AS 'itemCD',\n" +
                "	a.ITEM_VALUE AS 'itemValue',\n" +
                "	w.UPLOAD_FILE AS picn,\n" +
                "	a.ITEM_NAME_CN AS 'itemNameCN'\n" +
                "FROM\n" +
                "	a_kuanshi_p a\n" +
                "	LEFT JOIN w_upload_file_p w ON a.FILE_ID = w.FILE_ID \n" +
                "WHERE\n" +
                "	a.FILE_ID IS NOT NULL \n" +
                "	AND a.PARENT_ID = '" + id + "';";
            DataTable dt = SQLmtm.GetDataTable(sql);
            List<设计点图片Dto> 设计点图片Dtos = new List<设计点图片Dto>();
            foreach (DataRow dr in dt.Rows)
            {
                设计点图片Dtos.Add(new 设计点图片Dto(dr));
            }
            return 设计点图片Dtos;
        }

        /// <summary>
        /// 取得设计点中文名
        /// </summary>
        /// <param name="itemValue"></param>
        /// <returns></returns>
        public static String GetNameCN(String itemValue)
        {
            DataRow dr = SQLmtm.GetDataRow("SELECT PROPERTY_NAME_CN FROM a_fit_property_p WHERE PROPERTY_VALUE='" + itemValue + "'");
            if (dr == null)
            {
                return "";
            }
            else
            {
                return dr["PROPERTY_NAME_CN"].ToString();
            }
        }

        /// <summary>
        /// 去设计点信息
        /// </summary>
        /// <param name="itemcd">设计点</param>
        /// <param name="str">模糊查询字段</param>
        /// <returns></returns>
        public static List<设计点图片Dto> GetDesign(String itemcd, String str,String SHOP_ID,String OGNIZATION_ID)
        {
            SHOP_ID = SHOP_ID == "49" ? "46" : SHOP_ID;
            String sql = "SELECT\n" +
                " a.DESIGN_ID id,\n" +
                " a.STYLE_CATEGORY_CD styleCategoryCD,\n" +
                " a.FILE_ID fileID,\n" +
                " a.ITEM_CD itemCD,\n" +
                " a.ITEM_VALUE itemValue,\n" +
                " a.ITEM_NAME_CN itemNameCN,\n" +
                " file.UPLOAD_FILE \"picn\",\n" +
                " CONCAT( 'shirtmtm.com',file.FILE_PATH, file.UPLOAD_FILE ) \"picurl\",\n" +
                " a.ITEM_NAME_EN itemNameEN,\n" +
                " a.ITEM_NAME_JP itemNameJP,\n" +
                " IFNULL( a.ITEM_COST, 0 ) itemCost,\n" +
                " a.REMARKS remarks,\n" +
                " a.ENABLE_FLAG enableFlag,\n" +
                " a.DELETE_FLAG deleteFlag,\n" +
                " a.HAVETO_FLAG haveToFlag,\n" +
                " a.VERSION version,\n" +
                " a.CREATE_DATE createDate,\n" +
                " a.CREATE_USER \"createBy.id\",\n" +
                " a.UPDATE_USER \"updateBy.id\",\n" +
                " a.ITEM_SORT itemSort,\n" +
                " a.ITEM_CATEGORY_CD itemCategoryCD,\n" +
                " file.FILE_ID \"uploadFile.fileId\",\n" +
                " file.FILE_SOURCE \"uploadFile.fileSource\",\n" +
                " file.MODULE_KBN \"uploadFile.moduleKbn\",\n" +
                " file.FTP_FILE \"uploadFile.ftpFile\",\n" +
                " file.FILE_PATH \"uploadFile.filePath\",\n" +
                " CONCAT( p.FIRST_NAME, p.LAST_NAME ) \"updateBy.firstName\" \n" +
                "FROM\n" +
                " a_designoption_p a\n" +
                " LEFT JOIN a_login_user_p p ON a.UPDATE_USER = p.login_user_id\n" +
                " LEFT JOIN w_upload_file_p file ON a.FILE_ID = file.FILE_ID \n" +
                " LEFT JOIN a_ognization_desgin_r adr ON a.DESIGN_ID = adr.DESGIN_ID \n" +
                "WHERE\n" +
                " a.STYLE_CATEGORY_CD = 'STYLE_CATEGORY-SHIRT' and a.DESIGN_ID IN ( SELECT DESGIN_ID FROM a_shop_desgin_r WHERE SHOP_ID = "+SHOP_ID+" ) \n" +
                " AND a.ITEM_CD='" + itemcd + "'\n" +
                " AND a.DELETE_FLAG = 0 \n" +
                " -- AND adr.OGNIZATION_ID = "+ OGNIZATION_ID + " \n" +
                " AND (a.ITEM_VALUE LIKE '%" + str + "%' OR a.ITEM_NAME_CN LIKE '%" + str + "%') " +
                "ORDER BY\n" +
                " a.item_sort,\n" +
                " a.UPDATE_DATE DESC";
            DataTable dt = SQLmtm.GetDataTable(sql);
            List<设计点图片Dto> 设计点图片dtos = new List<设计点图片Dto>();
            foreach (DataRow dr in dt.Rows)
            {
                设计点图片dtos.Add(new 设计点图片Dto(dr));
            }
            return 设计点图片dtos;
        }

        /// <summary>
        /// build设计点
        /// </summary>
        /// <param name="styleid"></param>
        /// <returns></returns>
        public static List<设计点DTO> Get设计点DTOs(String styleid)
        {
            String sql = "SELECT\n" +
                "	sr.*,\n" +
                "	adp.ITEM_NAME_CN AS OPTION_NAME,\n" +
                "	adp1.ITEM_NAME_CN AS ITEM_NAME \n" +
                "FROM\n" +
                "	s_style_option_r AS sr\n" +
                "	LEFT JOIN a_designoption_p AS adp ON sr.OPTION_VALUE = adp.ITEM_VALUE\n" +
                "	LEFT JOIN a_designoption_p AS adp1 ON sr.ITEM_VALUE = adp1.ITEM_VALUE \n" +
                "WHERE\n" +
                "	sr.SYS_STYLE_ID = '" + styleid + "';";
            DataTable dt = SQLmtm.GetDataTable(sql);
            List<设计点DTO> 设计点dtos = new List<设计点DTO>();
            foreach (DataRow dr in dt.Rows)
            {
                设计点dtos.Add(new 设计点DTO(dr));
            }
            return 设计点dtos;
        }
        /// <summary>
        /// 修改设计点
        /// </summary>
        /// <param name="Dto设计点"></param>
        /// <param name="styleid"></param>
        public static void Change设计点(Dto设计点 Dto设计点, String styleid)
        {
            SQLmtm.DoUpdate("s_style_option_r", new string[] { "OPTION_VALUE" }, new string[] { Dto设计点.OPTION_VALUE }, new string[] { "SYS_STYLE_ID", "ITEM_VALUE" }, new string[] { styleid, Dto设计点.ITEM_VALUE });
        }

        public static List<DTO款式配置设计点展现> GetDTOsByStyleID(String styleid)
        {
            List<DTO款式配置设计点展现> dTOs = new List<DTO款式配置设计点展现>();
            String sql = "SELECT\n" +
                    "	s1.id,\n" +
                    "	s2.id AS idd,\n" +
                    "	s1.STYLEID,\n" +
                    "	s1.STYLE_NAME_CN,\n" +
                    "	s1.ITEM_CD AS P_ITEM_CD,\n" +
                    "	s1.ITEM_VALUE AS P_ITEM_VALUE,\n" +
                    "	s1.ITEM_NAME_CN AS P_ITEM_NAME_CN,\n" +
                    "	s1.DEFAULT_CD,\n" +
                    "	s1.DEFAULT_VALUE,-- s2.ITEM_CD,\n" +
                    "	s1.DEFAULT_NAME_CN,\n" +
                    "	s2.ITEM_VALUE AS C_ITEM_VALUE,\n" +
                    "	s2.ITEM_NAME_CN AS C_ITEM_NAME_CN,\n" +
                    "	CONCAT( 'shirtmtm.com', file.FILE_PATH, file.UPLOAD_FILE ) AS `PICURL`,\n" +
                    "	file.UPLOAD_FILE AS `PICN` \n" +
                    "FROM\n" +
                    "	a_kuanshi_p AS s1\n" +
                    "	LEFT JOIN a_kuanshi_p AS s2 ON s1.id = s2.PARENT_ID\n" +
                    "	LEFT JOIN a_designoption_p AS a ON s2.ITEM_VALUE = a.ITEM_VALUE\n" +
                    "	LEFT JOIN w_upload_file_p AS file ON a.FILE_ID = file.FILE_ID \n" +
                    "WHERE\n" +
                    "	s1.STYLEID = '" + styleid + "';";
            foreach (DataRow dr in SQLmtm.GetDataTable(sql).Rows)
            {
                dTOs.Add(new DTO款式配置设计点展现(dr));
            }
            return dTOs;
        }
        public static Boolean SaveExcelToTable(List<DTO款式配置设计点展现> 设计点图片Dtos, String styleid, String stylename, bool hasTitle = false)
        {
            //try
            //{
            OpenFileDialog openFile = new OpenFileDialog();
            //openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            openFile.Filter = MIMEConst.EXCEL_MIME;
            openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            openFile.Multiselect = false;
            if (openFile.ShowDialog() == DialogResult.Cancel) return false;
            var filePath = openFile.FileName;
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return false;
            using (DataSet ds = new DataSet())
            {
                string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
                                "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                                "data source={3};",
                                (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);
                OleDbConnection myConnnn = new OleDbConnection(strCon);
                myConnnn.Open();
                DataTable dtDATA = myConnnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                String 工作簿名称 = dtDATA.Rows[0][2].ToString().Trim();
                string strCom = " SELECT * FROM [" + 工作簿名称 + "]";
                using (OleDbConnection myConn = new OleDbConnection(strCon))
                using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
                {
                    myConn.Open();
                    myCommand.Fill(ds);
                }
                if (ds == null || ds.Tables.Count <= 0) return false;
                DataTable dt = ds.Tables[0];
                List<设计点Excel格式> Exceldtos = new List<设计点Excel格式>();
                foreach (DataRow dr in dt.Rows)
                {
                    if (!String.IsNullOrWhiteSpace(dr[0].ToString()))
                    {
                        Exceldtos.Add(new 设计点Excel格式(dr));
                    }
                }
                List<DTO款式配置设计点展现> Showdtos = new List<DTO款式配置设计点展现>();
                foreach (设计点Excel格式 dto in Exceldtos)
                {
                    Showdtos.Add(new DTO款式配置设计点展现(dto, styleid, stylename));
                }
                款式配置设计点数据清洗Model model = new 款式配置设计点数据清洗Model(Showdtos);
                foreach (DTO款式配置设计点展现 dTO in 设计点图片Dtos)
                {
                    SQLmtm.ExecuteSql("DELETE FROM a_kuanshi_p WHERE id = " + dTO.id + ";");
                    SQLmtm.ExecuteSql("DELETE FROM a_kuanshi_p WHERE id = " + dTO.idd + ";");
                }
                model.UpdataVALUES();
                return true;
            }
            //}
            //catch
            //{
            //    return false;
            //}
        }
        /// <summary>
        /// 导出Excel用
        /// </summary>
        /// <param name="Dtos"></param>
        /// <returns></returns>
        public static List<设计点Excel格式> ListForExcel(List<DTO款式配置设计点展现> Dtos)
        {
            List<设计点Excel格式> Exceldtos = new List<设计点Excel格式>();
            Dtos.Sort(new CComparer());
            String item_value = "";
            foreach (DTO款式配置设计点展现 Dto in Dtos)
            {
                item_value = Dto.DEFAULT_VALUE;
                if (Dto.C_ITEM_VALUE == item_value)
                {
                    Exceldtos.Add(new 设计点Excel格式(Dto, true));
                }
                else
                {
                    Exceldtos.Add(new 设计点Excel格式(Dto, false));
                }
            }
            return Exceldtos;
        }
        
        //public static Boolean Updata设计点配置(List<DTO款式配置设计点展现> dTOs)
        //{
        //    List<String> ITEM_VALUEs = new List<String>();
        //    try
        //    {
        //        foreach(DTO款式配置设计点展现 dTO in dTOs)
        //        {
        //            if (ITEM_VALUEs.Contains(dTO.P_ITEM_VALUE))
        //            {

        //            }
        //            else
        //            {
        //                ITEM_VALUEs.Add(dTO.P_ITEM_VALUE);
        //            }
        //        }
        //    }
        //    catch
        //    {

        //    }
        //}
        /// <summary>
        /// 工单信息的设计点
        /// </summary>
        /// <param name="orderid"></param>
        /// <returns></returns>
        public static DataTable Get工单信息设计点(String orderid)
        {
            String sql = "SELECT\n" +
                    "/*\n" +
                    " s_style_option_r.SYTLE_OPTION_ID,\n" +
                    " s_style_option_r.SYS_STYLE_ID,\n" +
                    " s_style_option_r.ITEM_CD,\n" +
                    " s_style_option_r.ITEM_VALUE,\n" +
                    " s_style_option_r.OPTION_VALUE,\n" +
                    " adp1.ITEM_NAME_CN,\n" +
                    " adp2.ITEM_NAME_CN,*/\n" +
                    "	CONCAT( s_style_option_r.ITEM_VALUE, ':', adp1.ITEM_NAME_CN ) AS ITEM,\n" +
                    "	CONCAT( s_style_option_r.OPTION_VALUE, ':', adp2.ITEM_NAME_CN ) AS `VALUE` \n" +
                    "FROM\n" +
                    "	s_style_option_r\n" +
                    "	LEFT JOIN a_designoption_p AS adp1 ON s_style_option_r.ITEM_VALUE = adp1.ITEM_VALUE\n" +
                    "	LEFT JOIN a_designoption_p AS adp2 ON s_style_option_r.OPTION_VALUE = adp2.ITEM_VALUE \n" +
                    "	AND s_style_option_r.ITEM_VALUE = adp2.ITEM_CD \n" +
                    "WHERE\n" +
                    "	s_style_option_r.SYS_STYLE_ID IN ( SELECT o_order_p.STYLE_ID FROM o_order_p WHERE o_order_p.ORDER_ID = '"+orderid+"' )";
            return SQLmtm.GetDataTable(sql);
        }

        public static DataRow Get工单信息面料(String styleid)
        {
            String sql = "SELECT\n" +
                    "	'面料' AS `ITEM`,\n" +
                    "	CONCAT( i_material_p.MATERIAL_CODE, ':', i_material_p.MATERIAL_NAME_CN ) AS 'VALUE' \n" +
                    "FROM\n" +
                    "	s_style_p\n" +
                    "	LEFT JOIN i_material_p ON s_style_p.SYTLE_FABRIC_ID = i_material_p.MATERIAL_ID \n" +
                    "WHERE\n" +
                    "	s_style_p.SYS_STYLE_ID = '" + styleid + "'";
            return SQLmtm.GetDataRow(sql);
        }

        public static List<Dto设计点> SortItemList(List<Dto设计点> Dto设计点s)
        {
            List<Dto设计点> Dtos = Dto设计点s;
            Dto设计点 Dto4SMO = new Dto设计点();
            Dto设计点 Dto4SMC = new Dto设计点();
            Dto设计点 Dto4SMP = new Dto设计点();
            Dto设计点 Dto4SMS = new Dto设计点();
            Dto设计点 Dto4SMT = new Dto设计点();

            foreach (Dto设计点 dto in Dto设计点s)
            {
                if (dto.ITEM_VALUE.Equals("4SMO"))
                //if (dto.ITEM_VALUE.Equals("4SMO") || dto.ITEM_VALUE.Equals("4SMC") || dto.ITEM_VALUE.Equals("4SMP") || dto.ITEM_VALUE.Equals("4SMS") || dto.ITEM_VALUE.Equals("4SMT"))
                {
                    Dtos.Remove(dto);
                    Dtos.Add(dto);
                    break;
                }
            }
            foreach (Dto设计点 dto in Dto设计点s)
            {
                if (dto.ITEM_VALUE.Equals("4SMC"))
                //if (dto.ITEM_VALUE.Equals("4SMO") || dto.ITEM_VALUE.Equals("4SMC") || dto.ITEM_VALUE.Equals("4SMP") || dto.ITEM_VALUE.Equals("4SMS") || dto.ITEM_VALUE.Equals("4SMT"))
                {
                    Dtos.Remove(dto);
                    Dtos.Add(dto);
                    break;
                }
            }
            foreach (Dto设计点 dto in Dto设计点s)
            {
                if (dto.ITEM_VALUE.Equals("4SMP"))
                //if (dto.ITEM_VALUE.Equals("4SMO") || dto.ITEM_VALUE.Equals("4SMC") || dto.ITEM_VALUE.Equals("4SMP") || dto.ITEM_VALUE.Equals("4SMS") || dto.ITEM_VALUE.Equals("4SMT"))
                {
                    Dtos.Remove(dto);
                    Dtos.Add(dto);
                    break;
                }
            }
            foreach (Dto设计点 dto in Dto设计点s)
            {
                if (dto.ITEM_VALUE.Equals("4SMS"))
                //if (dto.ITEM_VALUE.Equals("4SMO") || dto.ITEM_VALUE.Equals("4SMC") || dto.ITEM_VALUE.Equals("4SMP") || dto.ITEM_VALUE.Equals("4SMS") || dto.ITEM_VALUE.Equals("4SMT"))
                {
                    Dtos.Remove(dto);
                    Dtos.Add(dto);
                    break;
                }
            }
            foreach (Dto设计点 dto in Dto设计点s)
            {
                if (dto.ITEM_VALUE.Equals("4SMT"))
                //if (dto.ITEM_VALUE.Equals("4SMO") || dto.ITEM_VALUE.Equals("4SMC") || dto.ITEM_VALUE.Equals("4SMP") || dto.ITEM_VALUE.Equals("4SMS") || dto.ITEM_VALUE.Equals("4SMT"))
                {
                    Dtos.Remove(dto);
                    Dtos.Add(dto);
                    break;
                }
            }
            return Dtos;

        }
    }
}
